<?php
/*****************************************************************************
 *                                                                           *
 * Shop-Script PREMIUM                                                       *
 * Copyright (c) 2005 WebAsyst LLC. All rights reserved.                     *
 *                                                                           *
 *****************************************************************************/
?><?php
// *****************************************************************************
// Purpose	
// Inputs   
// Remarks		
// Returns	true if product exists
function prdProductExists( $productID )
{
	$q = db_query( "select count(*) from ".PRODUCTS_TABLE." where productID=".$productID );
	$row = db_fetch_row($q);
	return ($row[0]!=0);
}




// *****************************************************************************
// Purpose	gets product
// Inputs   $productID - product ID
// Remarks		
// Returns	array of fieled value 
//			"name"				- product name
//			"product_code"		- product code
//			"description"		- description
//			"brief_description"	- short description
//			"customers_rating"	- product rating
//			"in_stock"			- in stock (this parametr is persist if CONF_CHECKSTOCK == 1 )
//			"option_values"		- array of
//					"optionID"		- option ID
//					"name"			- name
//					"value"	- option value
//					"option_type" - option type
//			"ProductIsProgram"		- 1 if product is program, 0 otherwise
//			"eproduct_filename"		- program filename 
//			"eproduct_available_days"	- program is available days to download
//			"eproduct_download_times"	- attempt count download file
//			"weight"			- product weigth
//			"meta_description"		- meta tag description
//			"meta_keywords"			- meta tag keywords
//			"free_shipping"			- 1 product has free shipping, 
//							0 - otherwise
//			"min_order_amount"		- minimum order amount
//			"classID"			- tax class ID
function GetProduct( $productID, $AsIs = false )
{
	$productID = (int)$productID;
	$q = db_query("SELECT productID, categoryID, name, description, ".
				" customers_rating, Price, in_stock, ".
				" brief_description, list_price, ".
				" product_code, sort_order, date_added, ".
				" date_modified, default_picture, ".
				" eproduct_available_days, eproduct_download_times, ".
				" eproduct_filename, ".
				" weight, meta_description, meta_keywords, ".
				" free_shipping, min_order_amount, classID, shipping_freight, ".
				" customer_votes, enabled FROM ".
				PRODUCTS_TABLE.
				" WHERE productID='".$productID."'");
	if ( $product=db_fetch_row($q) )
	{
		$q1=db_query("SELECT optionID, name FROM ".
			PRODUCT_OPTIONS_TABLE);
		$product["option_values"] = array();
		while($option = db_fetch_row($q1))
		{
			$q2=db_query("SELECT option_value, option_type FROM ".
				PRODUCT_OPTIONS_VALUES_TABLE.
				" WHERE optionID='".$option["optionID"]."' AND ".
				" productID='".$productID."'" );
			if ( $option_value = db_fetch_row( $q2 ) )
			{			 
				$new_item = array();
				$new_item["optionID"]		= $option["optionID"];
				$new_item["name"]			= $option["name"];
				$new_item["value"]			= TransformDataBaseStringToText( $option_value["option_value"] );
				$new_item["option_type"]	= $option_value["option_type"];
				$product["option_values"][]	= $new_item;
			}
		}
		if(!$AsIs)$product["name"]				= TransformDataBaseStringToText( $product["name"] );
		$product["description"]			= TransformDataBaseStringToHTML_Text( $product["description"] );
		$product["brief_description"]	= TransformDataBaseStringToHTML_Text( $product["brief_description"] );
		$product["product_code"]		= TransformDataBaseStringToText( $product["product_code"] );
		$product["eproduct_filename"]	= TransformDataBaseStringToText( $product["eproduct_filename"] );
		$product["meta_description"]	= TransformDataBaseStringToText( $product["meta_description"] );
		$product["meta_keywords"]		= TransformDataBaseStringToText( $product["meta_keywords"] );

		$product["date_added"]			= format_datetime( $product["date_added"] );
		$product["date_modified"]		= format_datetime( $product["date_modified"] );

		return $product;
	}
	$product["ProductIsProgram"] = 	(trim($product["eproduct_filename"]) != "");
	return false;
}



// *****************************************************************************
// Purpose	updates product
// Inputs   $productID - product ID
//				$categoryID			- category ID ( see CATEGORIES_TABLE )
//				$name				- name of product
//				$Price				- price of product
//				$description		- product description
//				$in_stock			- stock counter
//				$customers_rating	- rating
//				$brief_description  - short product description
//				$list_price			- old price
//				$product_code		- product code
//				$sort_order			- sort order
//				$ProductIsProgram		- 1 if product is program, 0 otherwise
//				$eproduct_filename		- program filename 
//				$eproduct_available_days	- program is available days to download
//				$eproduct_download_times	- attempt count download file
//				$weight			- product weigth
//				$meta_description	- meta tag description
//				$meta_keywords		- meta tag keywords
//				$free_shipping		- 1 product has free shipping, 
//							0 - otherwise
//				$min_order_amount	- minimum order amount
//				$classID		- tax class ID
// Remarks		
// Returns	
function UpdateProduct( $productID, 
				$categoryID, $name, $Price, $description, 
				$in_stock, $customers_rating,
				$brief_description, $list_price,
				$product_code, $sort_order,
				$ProductIsProgram,
				$eproduct_filename, 
				$eproduct_available_days, 
				$eproduct_download_times,
				$weight, $meta_description, $meta_keywords,
				$free_shipping, $min_order_amount, $shipping_freight, $classID, $updateGCV = 1  )
{

	// special symbol prepare
	$name				= TransformStringToDataBase( $name );
	$description		= TransformStringToDataBase( $description );
	$brief_description	= TransformStringToDataBase( $brief_description );
	$product_code		= TransformStringToDataBase( $product_code );
	$eproduct_filename	= TransformStringToDataBase( $eproduct_filename );
	$meta_description	= TransformStringToDataBase( $meta_description );
	$meta_keywords		= TransformStringToDataBase( $meta_keywords );


	$weight = (float)$weight;
	$shipping_freight = (float)$shipping_freight;
	$min_order_amount = (int)$min_order_amount;
	if ( $min_order_amount == 0 )
		$min_order_amount = 1;
	$eproduct_available_days = (int)$eproduct_available_days;

	if ( !$ProductIsProgram )
		$eproduct_filename = "";
	if ( !$free_shipping ) $free_shipping = 0;
	else $free_shipping = 1;

	$q = db_query("select eproduct_filename from ".PRODUCTS_TABLE.
					" where productID=$productID");
	$old_file_name = db_fetch_row( $q );
	$old_file_name = $old_file_name[0];

	if ( $classID == null )
		$classID = "NULL";

	if ( $eproduct_filename != "" )
	{
		if ( trim($_FILES[$eproduct_filename]["name"]) != ""  )
		{
			if ( trim($old_file_name) != "" && file_exists("./products_files/$old_file_name") )
				unlink("./products_files/$old_file_name");

			if ( $_FILES[$eproduct_filename]["size"]!=0 )
					$r = move_uploaded_file($_FILES[$eproduct_filename]["tmp_name"], 
						"./products_files/".$_FILES[$eproduct_filename]["name"]);
			$eproduct_filename = trim($_FILES[$eproduct_filename]["name"]);
			SetRightsToUploadedFile( "./products_files/".$eproduct_filename );
		}
		else
			$eproduct_filename = $old_file_name;
	}
	else
		$eproduct_filename = $old_file_name;

	$s = "UPDATE ".PRODUCTS_TABLE." SET ".
				"categoryID='".$categoryID."', ".
				"name='".$name."', ".
				"Price='".$Price."', ".
				"description='".$description."', ".
				"in_stock='".$in_stock."', ".
				"customers_rating='".$customers_rating."', ".
				"brief_description='".$brief_description."', ".
				"list_price='".$list_price."', ".
				"product_code='".$product_code."', ".
				"sort_order='".$sort_order."', ".
				"date_modified='".get_current_time()."', ".
				"eproduct_filename='$eproduct_filename', ".
				"eproduct_available_days=$eproduct_available_days, ".
				"eproduct_download_times=$eproduct_download_times,  ".
				"weight=$weight, meta_description='$meta_description', ".
				"meta_keywords='$meta_keywords', free_shipping=$free_shipping, ".
				"min_order_amount = $min_order_amount, ".
				"shipping_freight = $shipping_freight ";

	if ($classID != null)
		$s .= ", classID = $classID ";

	$s .= "where productID='".$productID."'";
	db_query($s) or die (db_error());

	db_query("delete from ".CATEGORIY_PRODUCT_TABLE." where productID = '$productID' and categoryID = '$categoryID'") or die (db_error());

	if ($updateGCV == 1 && CONF_UPDATE_GCV == '1') //update goods count values for categories in case of regular file editing. do not update during import from excel
		update_products_Count_Value_For_Categories(1);
}

// *****************************************************************************
// Purpose	sets product file 
// Inputs   
// Remarks		
// Returns	
function SetProductFile( $productID, $eproduct_filename )
{
	db_query( "update ".PRODUCTS_TABLE." set eproduct_filename='".$eproduct_filename."' ".
			" where productID=".$productID );
	
}

// *****************************************************************************
// Purpose	adds product
// Inputs   
//				$categoryID			- category ID ( see CATEGORIES_TABLE )
//				$name				- name of product
//				$Price				- price of product
//				$description		- product description
//				$in_stock			- stock counter
//				$brief_description  - short product description
//				$list_price			- old price
//				$product_code		- product code
//				$sort_order			- sort order
//				$ProductIsProgram		- 1 if product is program, 
//									0 otherwise
//				$eproduct_filename		- program filename ( it is index of $_FILE variable )
//				$eproduct_available_days	- program is available days 
//									to download
//				$eproduct_download_times	- attempt count download file
//				$weight			- product weigth
//				$meta_description	- meta tag description
//				$meta_keywords		- meta tag keywords
//				$free_shipping		- 1 product has free shipping, 
//							0 - otherwise
//				$min_order_amount	- minimum order amount
//				$classID		- tax class ID
// Remarks		
// Returns	
function AddProduct( 
				$categoryID, $name, $Price, $description, 
				$in_stock, 
				$brief_description, $list_price,
				$product_code, $sort_order,
				$ProductIsProgram, $eproduct_filename, 
				$eproduct_available_days, $eproduct_download_times,
				$weight, $meta_description, $meta_keywords,
				$free_shipping, $min_order_amount, $shipping_freight, 
				$classID, $updateGCV = 1 )
{
	// special symbol prepare
	$name				= TransformStringToDataBase( $name );
	$description		= TransformStringToDataBase( $description );
	$brief_description	= TransformStringToDataBase( $brief_description );
	$product_code		= TransformStringToDataBase( $product_code );
	$eproduct_filename	= TransformStringToDataBase( $eproduct_filename );
	$meta_description	= TransformStringToDataBase( $meta_description );
	$meta_keywords		= TransformStringToDataBase( $meta_keywords );

	if ( $free_shipping )
		$free_shipping = 1;
	else
		$free_shipping = 0;

	if ( $classID == null )
		$classID = "NULL";	

	$weight = (float)$weight;
	$min_order_amount = (int)$min_order_amount;
	if ( $min_order_amount == 0 )
		$min_order_amount = 1;
	$eproduct_available_days = (int)$eproduct_available_days;

	if ( !$ProductIsProgram )
		$eproduct_filename = "";

	if ( $eproduct_filename != "" )
	{
		if ( trim($_FILES[$eproduct_filename]["name"]) != ""  )
		{
			if ( $_FILES[$eproduct_filename]["size"]!=0 )
					$r = move_uploaded_file($_FILES[$eproduct_filename]["tmp_name"], 
						"./products_files/".$_FILES[$eproduct_filename]["name"]);
			$eproduct_filename = trim($_FILES[$eproduct_filename]["name"]);
			SetRightsToUploadedFile( "./products_files/".$eproduct_filename );
		}
	}		

	$shipping_freight = (float)$shipping_freight;
	
	if ( trim($name) == "" ) $name = "?";
	db_query("INSERT INTO ".PRODUCTS_TABLE.
		" ( categoryID, name, description,".
		"	customers_rating, Price, in_stock, ".
		"	customer_votes, items_sold, enabled, ".
		"	brief_description, list_price, ".
		"	product_code, sort_order, date_added, ".
		" 	eproduct_filename, eproduct_available_days, ".
		" 	eproduct_download_times, ".
		"	weight, meta_description, meta_keywords, ".
		"	free_shipping, min_order_amount, shipping_freight, classID ".
		" ) ".
		" VALUES ('".
				$categoryID."','".
				$name."','".
				$description."', ".
				"0, '".
				$Price."', '".
				$in_stock."', ".
				" 0, 0, 1, '".
				$brief_description."', '".
				$list_price."', '".
				$product_code."', '".
				$sort_order."', '".
				get_current_time()."',  ".
				"'".$eproduct_filename."', ".
				$eproduct_available_days.", ".
				$eproduct_download_times.",  ".
				$weight.", ".
				"'".$meta_description."', ".
				"'".$meta_keywords."', ".
				$free_shipping.", ".
				$min_order_amount.", ".
				$shipping_freight.", ".
				$classID." ".
			");" );
	$insert_id = db_insert_id();
	if ( $updateGCV == 1 && CONF_UPDATE_GCV == '1')
		update_products_Count_Value_For_Categories(1);
	return $insert_id;
}


// *****************************************************************************
// Purpose	deletes product
// Inputs   $productID - product ID
// Remarks		
// Returns	true if success, else false otherwise
function DeleteProduct($productID, $updateGCV = 1)
{
	$whereClause = " where productID='".$productID."'";

	$q = db_query( "select itemID from ".SHOPPING_CART_ITEMS_TABLE." ".$whereClause );
	while( $row=db_fetch_row($q) )
		db_query( "delete from ".SHOPPING_CARTS_TABLE." where itemID=".$row["itemID"] );

	// delete all items for this product
	db_query("update ".SHOPPING_CART_ITEMS_TABLE.
		" set productID=NULL ".$whereClause);

	// delete all product option values
	db_query("delete from ".PRODUCTS_OPTIONS_SET_TABLE.$whereClause);	
	db_query("delete from ".PRODUCT_OPTIONS_VALUES_TABLE.$whereClause);

	// delete pictures
	db_query("delete from ".PRODUCT_PICTURES.$whereClause);

	// delete additional categories records
	db_query("delete from ".CATEGORIY_PRODUCT_TABLE.$whereClause);

	// delete discussions
	db_query("delete from ".DISCUSSIONS_TABLE.$whereClause);

	// delete special offer
	db_query("delete from ".SPECIAL_OFFERS_TABLE.$whereClause);

	// delete related items
	db_query("delete from ".RELATED_PRODUCTS_TABLE.$whereClause );
	db_query("delete from ".RELATED_PRODUCTS_TABLE." where Owner=$productID");

	// delete product
	db_query("delete from ".PRODUCTS_TABLE.$whereClause);


	if ( $updateGCV == 1 && CONF_UPDATE_GCV == '1')
		update_products_Count_Value_For_Categories(1);

	return true;
}


// *****************************************************************************
// Purpose	deletes all products of category
// Inputs   $categoryID - category ID
// Remarks		
// Returns	true if success, else false otherwise
function DeleteAllProductsOfThisCategory($categoryID)
{
	$q=db_query("select productID from ".PRODUCTS_TABLE.
			" where categoryID='".$categoryID."'");
	$res=true;
	while( $r=db_fetch_row( $q ) )
	{
		if ( !DeleteProduct( $r["productID"], 0 ) )
			$res = false;
	}

	if ( CONF_UPDATE_GCV == '1')
		update_products_Count_Value_For_Categories(1);

	return $res;
}


// *****************************************************************************
// Purpose	gets extra parametrs
// Inputs   $productID - product ID
// Remarks		
// Returns	array of value extraparametrs
//				each item of this array has next struture
//					first type "option_type" = 0
//						"name"					- parametr name
//						"option_value"			- value
//						"option_type"			- 0
//					second type "option_type" = 1
//						"name"					- parametr name
//						"option_show_times"		- how times does show in client side this
//												parametr to select
//						"variantID_default"		- variant ID by default
//						"values_to_select"		- array of variant value to select
//							each item of "values_to_select" array has next structure
//								"variantID"			- variant ID
//								"price_surplus"		- to added to price
//								"option_value"		- value
function GetExtraParametrs( $productID )
{
	$extra = array();
	$q=db_query("select optionID, name from ".
		PRODUCT_OPTIONS_TABLE." order by sort_order, name");
	while ($row = db_fetch_row($q))
	{
		if ($row["name"]!="")
		{
			$q1=db_query("select option_value, option_type, ". 
				    "option_show_times, variantID, optionID from ".
						PRODUCT_OPTIONS_VALUES_TABLE.
				    " where productID='$productID' AND optionID=$row[0]");
			$val = db_fetch_row($q1);
			$b=null;
			if ($val)
			{
				if ( ($val["option_type"]==0 || $val["option_type"]==NULL) &&
					strlen( trim($val["option_value"]) ) > 0/* && 
					$val["option_value"] != null*/  )
				{
					$b = array();
					$b["option_type"] = $val["option_type"];
					$b["name"] = $row["name"];
					$b["option_value"] = $val["option_value"];
					$b["option_value"] = TransformDataBaseStringToText( $b["option_value"] );
				}
				else if ( $val["option_type"]==1 )
				{				
					$show_option_price=1;
					$b = array();
					$b["optionID"] = $val["optionID"];
					$b["option_type"] = $val["option_type"];
					$b["name"] = $row["name"];
					$b["option_show_times"] = $val["option_show_times"];
					$b["variantID"] = $val["variantID"];



					//fetch all option values variants
					$q2=db_query( "select option_value, variantID from ".
							PRODUCTS_OPTIONS_VALUES_VARIANTS_TABLE.
							" where optionID=".$row[0]." order by sort_order, option_value" );
					$b["values_to_select"]=array();
					$i=0;
					while( $value = db_fetch_row($q2)  )
					{
						//is this value is product's allowed values list
						$q3 = db_query("select variantID, price_surplus from ".
						PRODUCTS_OPTIONS_SET_TABLE.
						" where productID='$productID' AND optionID=".$row[0]." and variantID=".$value["variantID"] );
						$values_to_select = db_fetch_row($q3);

						if ($values_to_select)
						{
							$b["values_to_select"][$i]=array();
							$b["values_to_select"][$i]["option_value"] = TransformDataBaseStringToText( $value["option_value"] );
							if ( $values_to_select["price_surplus"] > 0 )
								$b["values_to_select"][$i]["option_value"] .= " (+ ".show_price($values_to_select["price_surplus"]).")";
							else if ( $values_to_select["price_surplus"] < 0 )
								$b["values_to_select"][$i]["option_value"] .= " (- ".show_price(-$values_to_select["price_surplus"]).")";

							$b["values_to_select"][$i]["option_valueWithOutPrice"] = $value["option_value"];
							$b["values_to_select"][$i]["price_surplus"]=
								show_priceWithOutUnit(
									$values_to_select["price_surplus"]);
							$b["values_to_select"][$i]["variantID"]=
								$values_to_select["variantID"];
							$i++;

						}
					}


				}
				if ( !is_null($b) )
				{
					$extra[] = $b;
				}
			}
		}
	}
	return $extra;
}


function _setPictures( & $product )
{
	if ( !is_null($product["default_picture"]) )
	{
		$pictire=db_query("select filename, thumbnail, enlarged from ".
					PRODUCT_PICTURES." where photoID=".$product["default_picture"] );
		$pictire_row=db_fetch_row($pictire);
		$product["picture"]=$pictire_row["filename"];
		$product["thumbnail"]=$pictire_row["thumbnail"];
		$product["big_picture"]=$pictire_row["enlarged"];
		if (!file_exists("./products_pictures/".$product["picture"])) $product["picture"]=0;
		if (!file_exists("./products_pictures/".$product["thumbnail"])) $product["thumbnail"]=0;
		if (!file_exists("./products_pictures/".$product["big_picture"])) $product["big_picture"]=0;
	}
}


function GetProductInSubCategories( $callBackParam, &$count_row, $navigatorParams = null )
{

	if ( $navigatorParams != null )
	{
		$offset			= $navigatorParams["offset"];
		$CountRowOnPage	= $navigatorParams["CountRowOnPage"];
	}
	else
	{
		$offset = 0;
		$CountRowOnPage = 0;
	}

	$categoryID	= $callBackParam["categoryID"];
	$subCategoryIDArray = catGetSubCategories( $categoryID );
	$cond = "";
	foreach( $subCategoryIDArray as $subCategoryID )
	{
		if ( $cond != "" )
			$cond .= " OR categoryID=$subCategoryID";
		else 
			$cond .= " categoryID=$subCategoryID ";
	}
	$whereClause = "";
	if ( $cond != "" )
		$whereClause = " where ".$cond;

	$result = array();
	if ( $whereClause == "" )
	{
		$count_row = 0;
		return $result;
	}

	$q=db_query("select categoryID, name, brief_description, ".
	 		" customers_rating, Price, in_stock, ".
			" customer_votes, list_price, ".
			" productID, default_picture, sort_order from ".PRODUCTS_TABLE.
			" ".$whereClause." order by sort_order, name " );
	$i=0;
	while( $row=db_fetch_row($q) )
	{
		if ( ($i >= $offset && $i < $offset + $CountRowOnPage) || 
					 	$navigatorParams == null  )
		{
			$row["PriceWithUnit"]		= show_price($row["Price"]);
			$row["list_priceWithUnit"] 	= show_price($row["list_price"]);
			// you save (value)
			$row["SavePrice"]		= show_price($row["list_price"]-$row["Price"]); 

			// you save (%)
			if ($row["list_price"]) 
				$row["SavePricePercent"] = ceil(((($row["list_price"]-$row["Price"])/$row["list_price"])*100));

			_setPictures( $row );

			$row["product_extra"]=GetExtraParametrs($row["productID"]);
			$row["PriceWithOutUnit"]= show_priceWithOutUnit( $row["Price"] );
			$result[] = $row;
		}
		$i++;
	}
	$count_row = $i;
	return $result; 
}


// *****************************************************************************
// Purpose	gets all products by categoryID
// Inputs     	$callBackParam item
//			"categoryID"
//			"fullFlag"
// Remarks	
// Returns	
function prdGetProductByCategory( $callBackParam, &$count_row, $navigatorParams = null )
{

	if ( $navigatorParams != null )
	{
		$offset			= $navigatorParams["offset"];
		$CountRowOnPage	= $navigatorParams["CountRowOnPage"];
	}
	else
	{
		$offset = 0;
		$CountRowOnPage = 0;
	}

	$result = array();

	$categoryID	= $callBackParam["categoryID"];
	$fullFlag	= $callBackParam["fullFlag"];
	if ( $fullFlag )
	{
		$conditions = array( " categoryID=$categoryID " );
		$q = db_query("select productID from ".
				CATEGORIY_PRODUCT_TABLE." where  categoryID=$categoryID");
		while( $products = db_fetch_row( $q ) )
			$conditions[] = " productID=".$products[0];

		$data = array();
		foreach( $conditions as $cond )
		{
			$q=db_query("select categoryID, name, brief_description, ".
		 		" customers_rating, Price, in_stock, ".
				" customer_votes, list_price, ".
				" productID, default_picture, sort_order, items_sold, enabled, product_code from ".PRODUCTS_TABLE.
				" where ".$cond );
			while( $row = db_fetch_row($q) )
			{
				$row["PriceWithUnit"]		= show_price($row["Price"]);
				$row["list_priceWithUnit"] 	= show_price($row["list_price"]);
				// you save (value)
				$row["SavePrice"]		= show_price($row["list_price"]-$row["Price"]); 

				// you save (%)
				if ($row["list_price"]) 
					$row["SavePricePercent"] = ceil(((($row["list_price"]-$row["Price"])/$row["list_price"])*100));
				_setPictures( $row );
				$row["product_extra"]=GetExtraParametrs($row["productID"]);
				$row["PriceWithOutUnit"]= show_priceWithOutUnit( $row["Price"] );
				$data[] = $row;
			}
		}

		function _compare( $row1, $row2 )
		{
			 if ( (int)$row1["sort_order"] == (int)$row2["sort_order"] ) 
				return 0;
			 return ((int)$row1["sort_order"] < (int)$row2["sort_order"]) ? -1 : 1;
		}

		usort($data, "_compare");

		$result = array();
		$i = 0;
		foreach( $data as $res )
		{
			if ( ($i >= $offset && $i < $offset + $CountRowOnPage) || 
					$navigatorParams == null )
				$result[] = $res;
			$i++;
		}
		$count_row = $i;
		return $result;
	}
	else
	{
		$q=db_query("select categoryID, name, brief_description, ".
		 		" customers_rating, Price, in_stock, ".
				" customer_votes, list_price, ".
				" productID, default_picture, sort_order, items_sold, enabled, product_code from ".PRODUCTS_TABLE.
				" where categoryID=$categoryID order by sort_order, name" );
		$i=0;
		while( $row=db_fetch_row($q) )
		{
			if ( ($i >= $offset && $i < $offset + $CountRowOnPage) || 
				$navigatorParams == null  )
				$result[] = $row;
			$i++;
		}
		$count_row = $i;		
		return $result;
	}
}




function _getConditionWithCategoryConjWithSubCategories( $condition, $categoryID ) //fetch products from current category and all its subcategories
{
	$new_condition = "";
	$categoryID_Array = catGetSubCategories( $categoryID );
	$categoryID_Array[] = (int)$categoryID;
	foreach( $categoryID_Array as $catID )
	{
		if ( $new_condition != "" )
			$new_condition .= " OR ";
		$new_condition .= _getConditionWithCategoryConj($condition, $catID);
	}
	return $new_condition;
}


function _getConditionWithCategoryConj( $condition, $categoryID ) //fetch products from current category
{
	$category_condition = "";
	$q = db_query("select productID from ".
				CATEGORIY_PRODUCT_TABLE." where categoryID=$categoryID");
	while( $product = db_fetch_row( $q ) )
	{
		if ( $category_condition != "" )
			$category_condition .= " OR ";
		$category_condition .= " productID=".$product[0];
	}
	if (strlen($category_condition)>0) $category_condition = "(".$category_condition.")";

	if ( $condition == "" )
	{
		if ( $category_condition == "" )
			return "categoryID=".$categoryID;
		else
			return $category_condition." OR categoryID=".$categoryID;
	}
	else
	{
		if ( $category_condition == "" )
			return $condition." AND categoryID=".$categoryID;
		else
			return "( $condition AND $category_condition ) OR ".
				" ( $condition AND categoryID=$categoryID )";
	}
}


// *****************************************************************************
// Purpose	
// Inputs  
//				$productID - product ID
//				$template  - array of item
//					"optionID"	- option ID
//					"value"		- value or variant ID
// Remarks	
// Returns	returns true if product matches to extra parametr template
//			false otherwise
function _testExtraParametrsTemplate( $productID, $template )
{

	// get category ID
	$categoryID = $template["categoryID"];

	foreach( $template as $key => $item )
	{
		if ( !isset($item["optionID"]) )
			continue;

		if ( (string)$key == "categoryID" )
			continue;

		// get value to search
		$res = schOptionIsSetToSearch( $categoryID, $item["optionID"] );

		if ( $res["set_arbitrarily"] == 1 )
			$valueFromForm = $item["value"];
		else
		{
			if ( (int)$item["value"] == 0 )
				continue;
			$q = db_query("select option_value from ".
							PRODUCTS_OPTIONS_VALUES_VARIANTS_TABLE.
							" where variantID=".$item["value"]);
			$option_value = db_fetch_row($q);
			$valueFromForm = $option_value["option_value"];
		}

		// get option value
		$q = db_query( "select option_value, option_type from ".
			PRODUCT_OPTIONS_VALUES_TABLE.
			" where optionID=".$item["optionID"]." AND productID=$productID" );

		if (  !($row=db_fetch_row($q))  )
		{
			if ( trim($valueFromForm) == "" )
				continue;
			else
				return false;
		}

		$option_value	= $row["option_value"];
		$option_type	= $row["option_type"];
		$valueFromDataBase = array();
		if ( $option_type == 0 )
			$valueFromDataBase[] = $option_value;
		else
		{
			$q = db_query("select productID, optionID, variantID from ".
				PRODUCTS_OPTIONS_SET_TABLE.
				" where optionID=".$item["optionID"]." AND productID=$productID" );
			while( $row=db_fetch_row($q) )
			{
				$q1 = db_query("select option_value from ".
					PRODUCTS_OPTIONS_VALUES_VARIANTS_TABLE.
					" where variantID=".$row["variantID"] );
				while( $option_value=db_fetch_row($q1) )
					$valueFromDataBase[] = $option_value["option_value"];
			}
		}

		if ( trim($valueFromForm) != "" )
		{
			$existFlag = false;
			foreach( $valueFromDataBase as $value )
			{
				if (  strstr(	(string)trim($value),  (string)trim($valueFromForm)   )    )
				{
					$existFlag = true;
					break;
				}
			}
			if ( !$existFlag )
				return false;
		}
	}
	return true;
}



// *****************************************************************************
// Purpose	
// Inputs  
// Remarks	
// Returns	
function _deletePercentSymbol( &$str )
{
	$str = str_replace( "%", "", $str );
	return $str;
}


// *****************************************************************************
// Purpose	gets all products by categoryID
// Inputs     	$callBackParam item
//					"search_simple"				- string search simple
//					"sort"					- column name to sort
//					"direction"				- sort direction DESC - by descending,
//												by ascending otherwise
//					"searchInSubcategories" - if true function searches 
//						product in subcategories, otherwise it does not 
//					"searchInEnabledSubcategories"	- this parametr is actual when
//											"searchInSubcategories" parametr is specified
//											if true this function take in mind enabled categories only
//					"categoryID"	- is not set or category ID to be searched
//					"name"			- array of name template
//					"product_code"		- array of product code template
//					"price"			- 
//								array of item
//									"from"	- down price range
//									"to"	- up price range
//					"enabled"		- value of column "enabled" 
//									in database
//					"extraParametrsTemplate"
// Remarks	
// Returns	
function prdSearchProductByTemplate( 
				$callBackParam, &$count_row, $navigatorParams = null )
{
	// navigator params 
	if ( $navigatorParams != null )
	{
		$offset			= $navigatorParams["offset"];
		$CountRowOnPage	= $navigatorParams["CountRowOnPage"];
	}
	else
	{
		$offset = 0;
		$CountRowOnPage = 0;
	}


	// special symbol prepare
	if ( isset($callBackParam["search_simple"]) )
	{
		for( $i=0; $i<count($callBackParam["search_simple"]); $i++ )
		{
			$callBackParam["search_simple"][$i] = 
				TransformStringToDataBase( $callBackParam["search_simple"][$i] );
		}
		_deletePercentSymbol( $callBackParam["search_simple"] );
	}
	if ( isset($callBackParam["name"]) ) 
	{
		for( $i=0; $i<count($callBackParam["name"]); $i++ )
			$callBackParam["name"][$i] = 
				TransformStringToDataBase( $callBackParam["name"][$i] );
		_deletePercentSymbol( $callBackParam["name"][$i] );
	}
	if ( isset($callBackParam["product_code"]) )
	{
		for( $i=0; $i<count($callBackParam["product_code"]); $i++ )
		{
			$callBackParam["product_code"][$i] = 
				TransformStringToDataBase( $callBackParam["product_code"][$i] );
		}
		_deletePercentSymbol( $callBackParam["product_code"] );
	}

	if ( isset($callBackParam["extraParametrsTemplate"]) )
	{
		foreach( $callBackParam["extraParametrsTemplate"] as $key => $value )
		{
			if ( is_int($key) )
			{
				$callBackParam["extraParametrsTemplate"][$key] = 
					TransformStringToDataBase( $callBackParam["extraParametrsTemplate"][$key] );
				_deletePercentSymbol( $callBackParam["extraParametrsTemplate"][$key] );
			}
		}
	}


	$where_clause = "";

	if ( isset($callBackParam["search_simple"]) )
	{
		if (!count($callBackParam["search_simple"])) //empty array
		{
			$where_clause = " where 0";
		}
		else //search array is not empty
		{
			foreach( $callBackParam["search_simple"] as $value )
			{
				if ( $where_clause != "" )
					$where_clause .= " AND ";
				$where_clause .= " ( LOWER(name) LIKE '%".strtolower($value)."%' OR ".
						 "   LOWER(description) LIKE '%".strtolower($value)."%' OR ".
						 "   LOWER(brief_description) LIKE '%".strtolower($value)."%' ) ";
			}

			if ( $where_clause != "" )
			{
				$where_clause = " where categoryID>1 and enabled=1 and ".$where_clause;
			}
			else
			{
				$where_clause = "where categoryID>1 and enabled=1";
			}
		}

	}
	else
	{

		// "enabled" parameter
		if ( isset($callBackParam["enabled"]) )
		{
			if ( $where_clause != "" )
				$where_clause .= " AND ";
			$where_clause.=" enabled=".$callBackParam["enabled"];
		}

		// take into "name" parameter
		if ( isset($callBackParam["name"]) )
		{
			foreach( $callBackParam["name"] as $name )
				if (strlen($name)>0)
				{
					if ( $where_clause != "" )
						$where_clause .= " AND ";			
					$where_clause .= " name LIKE '%".$name."%' ";
				}
		}

		// take into "product_code" parameter
		if ( isset($callBackParam["product_code"]) )
		{
			foreach( $callBackParam["product_code"] as $product_code )
			{
				if ( $where_clause != "" )
					$where_clause .= " AND ";
				$where_clause .= " product_code LIKE '%".$product_code."%' ";
			}
		}

		// take into "price" parameter
		if ( isset($callBackParam["price"]) )
		{
			$price = $callBackParam["price"];

			if ( trim($price["from"]) != "" && $price["from"] != null )
			{
				if ( $where_clause != "" )
					$where_clause .= " AND ";
				$from	= ConvertPriceToUniversalUnit( $price["from"] );
				$where_clause .= "$from<=Price ";
			}
			if ( trim($price["to"]) != "" && $price["to"] != null )
			{
				if ( $where_clause != "" )
					$where_clause .= " AND ";
				$to		= ConvertPriceToUniversalUnit( $price["to"] );
				$where_clause .= " Price<=$to ";
			}
		}

		// categoryID
		if ( isset($callBackParam["categoryID"]) )
		{
			$searchInSubcategories = false;
			if ( isset($callBackParam["searchInSubcategories"]) )
			{
				if ( $callBackParam["searchInSubcategories"] )
					$searchInSubcategories = true;
				else
					$searchInSubcategories = false;
			}

			if ( $searchInSubcategories )
			{
				$where_clause = _getConditionWithCategoryConjWithSubCategories( $where_clause, 
											$callBackParam["categoryID"] );
			}
			else
			{
				$where_clause = _getConditionWithCategoryConj( $where_clause, 
											$callBackParam["categoryID"] );
			}
		}

		if ( $where_clause != "" )
			$where_clause = "where ".$where_clause;

	}


	$order_by_clause = "order by sort_order, name";
	if ( isset($callBackParam["sort"]) )
	{
		if (	$callBackParam["sort"] == "categoryID"			|| 
				$callBackParam["sort"] == "name"				||
				$callBackParam["sort"] == "brief_description"	||
				$callBackParam["sort"] == "in_stock"			||
				$callBackParam["sort"] == "Price"				||
				$callBackParam["sort"] == "customer_votes"		||
				$callBackParam["sort"] == "customers_rating"	||
				$callBackParam["sort"] == "list_price"			||
				$callBackParam["sort"] == "sort_order"			||
				$callBackParam["sort"] == "items_sold"			||
				$callBackParam["sort"] == "product_code"		||
				$callBackParam["sort"] == "shipping_freight"		)
		{
			$order_by_clause = " order by ".$callBackParam["sort"]." ASC ";
			if (  isset($callBackParam["direction"]) )
				if (  $callBackParam["direction"] == "DESC" )
					$order_by_clause = " order by ".$callBackParam["sort"]." DESC ";
		}
	}

	$sqlQueryCount = "select count(*) from ".PRODUCTS_TABLE.
				" $where_clause $order_by_clause";
	$q = db_query( $sqlQueryCount );
	$products_count = db_fetch_row($q);
	$products_count = $products_count[0];

	$sqlQuery = "select categoryID, name, brief_description, ".
		 		" customers_rating, Price, in_stock, ".
				" customer_votes, list_price, ".
				" productID, default_picture, sort_order, items_sold, enabled, ".
				" product_code, description, shipping_freight from ".PRODUCTS_TABLE.
				" $where_clause $order_by_clause";

	$q = db_query( $sqlQuery );
	$result = array();
	$i = 0;

	if ($offset >= 0 && $offset <= $products_count )
	{
		while( $row = db_fetch_row($q) )	
		{

			if ( isset($callBackParam["extraParametrsTemplate"]) )
			{

				// take into "extra" parametrs
				$testResult = _testExtraParametrsTemplate( $row["productID"], 
								$callBackParam["extraParametrsTemplate"] );
				if ( !$testResult ) 
					continue;
			}

			if ( ($i >= $offset && $i < $offset + $CountRowOnPage) || 
					$navigatorParams == null  )
			{
				$row["PriceWithUnit"]		= show_price($row["Price"]);
				$row["list_priceWithUnit"] 	= show_price($row["list_price"]);
				// you save (value)
				$row["SavePrice"]		= show_price($row["list_price"]-$row["Price"]); 

				// you save (%)
				if ($row["list_price"]) 
					$row["SavePricePercent"] = ceil(((($row["list_price"]-$row["Price"])/$row["list_price"])*100));
				_setPictures( $row );
				$row["product_extra"]		= GetExtraParametrs( $row["productID"] );
				$row["PriceWithOutUnit"]	= show_priceWithOutUnit( $row["Price"] );
				if ( ((float)$row["shipping_freight"]) > 0 )
					$row["shipping_freightUC"] = show_price( $row["shipping_freight"] );

				$row["name"]				= TransformDataBaseStringToText( $row["name"] );
				$row["description"]			= TransformDataBaseStringToHTML_Text( $row["description"] );
				$row["brief_description"]	= TransformDataBaseStringToHTML_Text( $row["brief_description"] );
				$row["product_code"]		= TransformDataBaseStringToText( $row["product_code"] );

				$result[] = $row;
			}
			$i++;
		}
	}
	$count_row = $i;
	return $result;
}


function prdGetMetaKeywordTag( $productID )
{
	$productID = (int)$productID;
	$q = db_query("select meta_description from ".	
			PRODUCTS_TABLE.
			" where productID=$productID" );
	if ( $row=db_fetch_row($q) )
		return TransformDataBaseStringToText( trim($row["meta_description"]) );
	else
		return "";
}

function prdGetMetaTags( $productID ) //gets META keywords and description - an HTML code to insert into <head> section
{
	$productID = (int) $productID;

	$q = db_query( "select meta_description, meta_keywords from ".
		PRODUCTS_TABLE." where productID=".$productID );
	$row = db_fetch_row($q);
	$meta_description	= TransformDataBaseStringToText( trim($row["meta_description"]) );
	$meta_keywords		= TransformDataBaseStringToText( trim($row["meta_keywords"]) );

	$res = "";

	if  ( $meta_description != "" )
		$res .= "<meta name=\"Description\" content=\"".str_replace("\"","&quot;",$meta_description)."\">\n";
	if  ( $meta_keywords != "" )
		$res .= "<meta name=\"KeyWords\" content=\"".str_replace("\"","&quot;",$meta_keywords)."\" >\n";

	return $res;
}
?>